{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "7874f912-50e7-4aa4-aa48-0d97584d48e3",
   "metadata": {},
   "source": [
    "![Banner](images/banner.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1dd36559-0d19-487a-a110-02b890ba7690",
   "metadata": {},
   "source": [
    "# Transaction Mananagement and Sessionless Transactions\n",
    "\n",
    "<hr>\n",
    "\n",
    "Setup for this notebook:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1f64d468-f213-4735-a8de-f3678cfc0491",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import oracledb\n",
    "\n",
    "un = os.environ.get(\"PYO_SAMPLES_MAIN_USER\", \"pythondemo\")\n",
    "pw = os.environ.get(\"PYO_SAMPLES_MAIN_PASSWORD\", \"welcome\")\n",
    "cs = os.environ.get(\"PYO_SAMPLES_CONNECT_STRING\", \"localhost/orclpdb\")\n",
    "\n",
    "connection = oracledb.connect(user=un, password=pw, dsn=cs)\n",
    "\n",
    "cursor = connection.cursor()\n",
    "try:\n",
    "    cursor.execute(\"drop table mytab\")\n",
    "except:\n",
    "    pass\n",
    "cursor.execute(\"create table mytab (id number, data varchar2(1000))\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5876c546-66e5-48cb-8acd-70890b4e74cb",
   "metadata": {},
   "source": [
    "## Transaction Management\n",
    "\n",
    "Documentation reference link: [Managing Transations](https://python-oracledb.readthedocs.io/en/latest/user_guide/txn_management.html)."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e0058aab-0804-4935-8600-1f7ed9afda91",
   "metadata": {},
   "source": [
    "A database transaction is a grouping of SQL statements that make a logical data change to the database. When statements like `Cursor.execute()` or `Cursor.executemany()` execute SQL statements like INSERT or UPDATE, a transaction is started or continued. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1cb74a36-8b89-42d4-927d-3840988ce1e0",
   "metadata": {},
   "outputs": [],
   "source": [
    "connection1 = oracledb.connect(user=un, password=pw, dsn=cs)\n",
    "cursor1 = connection1.cursor()\n",
    "cursor1.execute(\"insert into mytab (id, data) values (1, 'Widget')\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5a6e8bf3-4490-433d-b1c3-31ccdeacbc44",
   "metadata": {},
   "source": [
    "By default, python-oracledb does not commit this transaction to the database. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "18123b56-3923-4821-8138-57dbc1884439",
   "metadata": {},
   "outputs": [],
   "source": [
    "connection2 = oracledb.connect(user=un, password=pw, dsn=cs)\n",
    "cursor2 = connection2.cursor()\n",
    "\n",
    "# No rows will be displayed\n",
    "rows = cursor2.execute(\"select * from mytab\")\n",
    "for r in rows:\n",
    "    print(r)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9d38bb79-7417-43ca-92e1-670730f3ea72",
   "metadata": {},
   "source": [
    "You can explictly commit or roll it back using the methods `Connection.commit()` and `Connection.rollback()`.  \n",
    "\n",
    "To commit the row just inserted:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "76c39a92-aa91-4313-a4e1-a1ae5d521050",
   "metadata": {},
   "outputs": [],
   "source": [
    "connection1.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8272b6ec-721f-4fb6-8b24-1fdd55d071ba",
   "metadata": {},
   "source": [
    "The committed data is now visible in the second connection:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cfb399db-73f9-41c8-a0da-20dff5bc1268",
   "metadata": {},
   "outputs": [],
   "source": [
    "for r in cursor2.execute(\"select * from mytab\"):\n",
    "    print(r)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9a850d4a-e2c1-40a7-8203-fe1a24ea1f69",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Delete the data so the example can be re-run\n",
    "\n",
    "cursor1.execute(\"truncate table mytab\")\n",
    "connection1.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f8da302f-1720-48a0-bd06-cbabd7e41e7a",
   "metadata": {},
   "source": [
    "## Autocommitting\n",
    "\n",
    "Documentation reference link: [Autocommitting](https://python-oracledb.readthedocs.io/en/latest/user_guide/txn_management.html#autocommitting).\n",
    "\n",
    "Autocommitting is a way to improve application scalability by removing the need to perform an explicit `connection.commit()` call.\n",
    "\n",
    "The recommendation is to use it on the last DML statement of a transaction."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2586d903-cf7a-4427-a221-bd9afd31b451",
   "metadata": {},
   "outputs": [],
   "source": [
    "connection1 = oracledb.connect(user=un, password=pw, dsn=cs)\n",
    "cursor1 = connection1.cursor()\n",
    "\n",
    "connection1.autocommit = False  # make sure the value is off\n",
    "cursor1.execute(\"insert into mytab (id, data) values (1, 'Widget')\")\n",
    "\n",
    "# Insert a second row and commit the transaction\n",
    "connection1.autocommit = True\n",
    "cursor1.execute(\"insert into mytab (id, data) values (2, 'Thingummy')\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "88c0da81-2aea-4a45-92a2-d9df44c7e69b",
   "metadata": {},
   "source": [
    "Other sessions will see the committed data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c809bf41-3a20-4237-bad2-7f1e8c1b7353",
   "metadata": {},
   "outputs": [],
   "source": [
    "connection2 = oracledb.connect(user=un, password=pw, dsn=cs)\n",
    "cursor2 = connection2.cursor()\n",
    "\n",
    "for r in cursor2.execute(\"select * from mytab\"):\n",
    "    print(r)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2c1c30d8-1c03-493a-b3f0-cdf279d79c13",
   "metadata": {},
   "source": [
    "You should avoid the over-use of autocommit because it can break relational consistency. Also unnecessarily committing will add additional load the the database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0c91d7a9-f3dc-417a-a6e6-e635d746c912",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Delete the data so the example can be re-run\n",
    "\n",
    "cursor1.execute(\"truncate table mytab\")\n",
    "connection1.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ff4be0eb-7a2c-4b22-bdc5-facb2d0b454c",
   "metadata": {},
   "source": [
    "## Sessionless Transactions\n",
    "\n",
    "Documentation reference link: [Sessionless Transactions](https://python-oracledb.readthedocs.io/en/latest/user_guide/txn_management.html#sessionless-transactions).\n",
    "\n",
    "A Sessionless Transaction is a transaction that can be suspended and resumed during its lifecycle. A transaction is no longer tied to a specific connection. This enables connections to be released for use by other users while a transaction remains open to be resumed later. One use case is with web services, where a user's workflow involves calling individual services for specific tasks, and each service uses different tables for their part of the workflow. Each service can start, suspend, and resume a transaction as needed to complete the workflow in a single transaction, allowing relational consistency to be ensured. Another scenario is when a customer has some 'think time' between making purchases from an online store. During that intermediate time the customer is browsing the site, the connection can be released for other customers to use. This reduces the overall system resources required.\n",
    "\n",
    "With Sessionless Transactions, you do not need to use a transaction manager since Oracle Database manages coordination of transactions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ab09b1b5-9657-495a-86e6-063458bec61d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Start a transaction\n",
    "connection1 = oracledb.connect(user=un, password=pw, dsn=cs)\n",
    "txn_id = connection1.begin_sessionless_transaction()\n",
    "cursor1 = connection1.cursor()\n",
    "cursor1.execute(\"insert into mytab (id, data) values (1, 'Blue Dress')\")\n",
    "connection1.suspend_sessionless_transaction()\n",
    "\n",
    "# The user might have some 'think time' before doing more database work on the transaction\n",
    "# ...\n",
    "\n",
    "# In the same or another process, a second connection can resume and complete the transaction\n",
    "connection2 = oracledb.connect(user=un, password=pw, dsn=cs)\n",
    "connection2.resume_sessionless_transaction(txn_id)\n",
    "cursor2 = connection2.cursor()\n",
    "cursor2.execute(\"insert into mytab (id, data) values (2, 'Green Shirt')\")\n",
    "connection2.commit()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6a634fc6-1896-470d-af66-a850c1494a86",
   "metadata": {},
   "source": [
    "Both records have been committed to the database:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "72ad6411-a940-4f19-93a6-c89859b6b9b5",
   "metadata": {},
   "outputs": [],
   "source": [
    "connection3 = oracledb.connect(user=un, password=pw, dsn=cs)\n",
    "cursor3 = connection3.cursor()\n",
    "\n",
    "for r in cursor3.execute(\"select * from mytab\"):\n",
    "    print(r)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2ce9ea84-b19d-46de-a4ab-de96bef4b7fe",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Delete the data so the example can be re-run\n",
    "\n",
    "cursor1.execute(\"truncate table mytab\")\n",
    "connection1.commit()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.24"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
